﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using HySoft.Cash.Business;
using HySoft.Cash.Structure;
using HySoft.Cash;

namespace PayRoller.Include
{
    public partial class UImpPay : System.Web.UI.UserControl
    {
        // Methods
        protected void bnUpload_Click(object sender, EventArgs e)
        {
            lblTip.Text = string.Empty;
            if ((this.FileUpload1.PostedFile.FileName != null) && (this.FileUpload1.PostedFile.FileName != ""))
            {
                string fullName = this.FileUpload1.PostedFile.FileName;
                string extensionName = "";
                int sepratorPosition = fullName.LastIndexOf(".");
                extensionName = fullName.Substring(sepratorPosition + 1);
                string newFileName = "";
                if (extensionName == "xls")
                {
                    newFileName = Guid.NewGuid().ToString() + "." + extensionName;
                    string filePath = base.Server.MapPath("~/" + ConfigurationManager.AppSettings["upPath"] + "/" + newFileName);
                    this.FileUpload1.PostedFile.SaveAs(filePath);
                    //string filePath = ConfigurationManager.AppSettings["upPath2"] + "/" + newFileName;
                    DataSet dsPayment = this.ExcelToDS(filePath);
                    List<string> errorInfos;
                    int orgSid = 0;
                    if (base.Request["OrgSid"] != null)
                    {
                        orgSid = Convert.ToInt32(base.Request["OrgSid"]);
                    }
                    else
                    {
                        if ((HttpContext.Current.Request.Cookies["Company.ID"] != null) && (HttpContext.Current.Request.Cookies["Company.ID"].Value != ""))
                        {
                            orgSid = Convert.ToInt32(HttpContext.Current.Request.Cookies["Company.ID"].Value);
                        }
                    }

                    this.ImportEmpBank(dsPayment, orgSid, out errorInfos);
                    if (errorInfos.Count == 0)
                    {
                        this.Page.ClientScript.RegisterStartupScript(base.GetType(), "", "<script>alert('Success!');</script>");
                    }
                    else
                    {
                        this.lblTip.Text = string.Empty;
                        foreach (string current in errorInfos)
                        {
                            this.lblTip.Text += current + "\r\n";
                        }
                    }

                }
                else
                {
                    this.Page.ClientScript.RegisterStartupScript(base.GetType(), "", "<script>alert('Select Excel File,Pleaase!');</script>");
                }
            }
            else
            {
                this.Page.ClientScript.RegisterStartupScript(base.GetType(), "", "<script>alert('Select Excel File,Pleaase!');</script>");
            }
        }

        public DataSet ExcelToDS(string Path)
        {
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;";
            //new OleDbConnection(connectionString).Open();
            string selectCommandText = "";
            OleDbDataAdapter adapter = null;
            DataSet dataSet = null;
            selectCommandText = "select * from [sheet1$]";
            adapter = new OleDbDataAdapter(selectCommandText, connectionString);
            dataSet = new DataSet();
            adapter.Fill(dataSet, "table1");
            return dataSet;
        }

        public void ImportEmpBank(DataSet ds, int orgSid, out List<string> errorInfos)
        {
            errorInfos = new List<string>();
            DataTable table = ds.Tables[0];
            //从第二行开始，是真正的数据信息
            for (int i = 2; i < table.Rows.Count; i++)
            {
                bool isSuccessfulCurrent = true;
                try
                {
                    string strEmployeeName = table.Rows[i][0].ToString();
                    string strEmployeeID = table.Rows[i][1].ToString();
                    
                    string strAccountName = table.Rows[i][3].ToString();
                    string strBSB = table.Rows[i][4].ToString();
                    string strACNumber = table.Rows[i][5].ToString();
                    string strReference = table.Rows[i][7].ToString();
                    
                    string strPaymentDate = table.Rows[i][2].ToString();
                    DateTime paymentDate = DateTime.MinValue;
                    try
                    {
                        paymentDate= Convert.ToDateTime(strPaymentDate, AppSettings.SystemCulture);
                    }
                    catch
                    {
                        isSuccessfulCurrent = false;
                        errorInfos.Add(string.Format("Incorrent date format information for {0}[{1}] ", strEmployeeName, strEmployeeID));
                        continue;
                    }

                    string strAmount = table.Rows[i][6].ToString();
                    decimal paymentAmount = 0;
                    try
                    {
                        paymentAmount = Convert.ToDecimal(strAmount);
                    }
                    catch
                    {
                        isSuccessfulCurrent = false;
                        errorInfos.Add(string.Format("Incorrent amount format information for {0}[{1}] ", strEmployeeName, strEmployeeID));
                        continue;
                    }

                    int targetEmpSid = 0;
                    int targetBankID = 0;
                    using (TempinfoBN obn = new TempinfoBN(this.Page))
                    {
                        obn.Filter = string.Format("OrgEmpSid='{0}' and EmpName='{1}' and OrgSid={2}", strEmployeeID, strEmployeeName, orgSid);
                        DataTable dtList = obn.GetList();
                        if (dtList != null && dtList.Rows.Count > 0)
                        {
                            targetEmpSid = (int)(dtList.Rows[0][0]);
                        }
                        else
                        {
                            isSuccessfulCurrent = false;
                            errorInfos.Add(string.Format("No employee information for {0}[{1}] ", strEmployeeName, strEmployeeID));
                            continue;
                        }

                        obn.Close();
                        obn.Dispose();
                    }

                    using (TbankinfoBN obn2 = new TbankinfoBN(this.Page))
                    {
                        obn2.Filter = string.Format("EmpSid={0} and AccountName='{1}' and AccountNum='{2}' and Bsb='{3}'", targetEmpSid, strAccountName, strACNumber,strBSB);
                        DataTable dtBank = obn2.GetList();
                        if (dtBank != null && dtBank.Rows.Count > 0)
                        {
                            targetBankID = (int)dtBank.Rows[0][0];
                        }
                        else
                        {
                            isSuccessfulCurrent = false;
                            errorInfos.Add(string.Format("No bank information for {0}[{1}] ", strEmployeeName, strEmployeeID));
                            continue;
                        }

                        obn2.Close();
                        obn2.Dispose();
                    }

                    using (TemppaymentBN tbn = new TemppaymentBN(this.Page))
                    {
                        TemppaymentDT tdt = new TemppaymentDT();
                        tdt.EmpSid = targetEmpSid;
                        tdt.BankSid = targetBankID;
                        tdt.PaymentDate = paymentDate;
                        tdt.OrgSid = orgSid;
                        
                        tdt.PaymentAmount = paymentAmount;
                        tbn.Add(tdt);
                        tbn.Close();
                        tbn.Dispose();
                    }
                }
                catch (Exception exception)
                {
                    //this.lblTip.Text = exception.Message;
                }
            }
        }
    }
}